<?php
require_once 'Zend/Db/Table/Abstract.php';
class PointModel extends Zend_Db_Table_Abstract
{
	protected $_name = 'point';
	protected $_id = 'id';
	public function room(){
		$db = Zend_Registry::get('connectDB');
		$mySql = $db->select()
					->from('point');
		$list = $db->fetchAll($mySql);
		return $list;
	}

	/*
	 * Ham them diem sinh vien
	 */
	public function add($point){
		$model = new PointModel();
		$data = array('id_students'=>$point['id_students'],
					'point'=>$point['point'],
					'id_exercises'=>$point['id_exercises'],
					'note'=>$point['note']);
		$id = $model->insert($data);
		return $id;
	}

	/*
	* Ham them diem cho sing vien full feild
	*/

	public function addPoint($point){
		$model = new PointModel();
		$data = array('id_students'=>$point['id_students'],
					'point'=>$point['point'],
					'id_exercises'=>$point['id_exercises'],
					'id_subjects'=>$point['id_subjects'],
					'info_extend'=>$point['info_extend'],
					'note'=>$point['note']);
		$id = $model->insert($data);
		return $id;
	}

	/*
     * Ham lay thong tin về điểm của sinh vien qua id bai tap
     */  
    public function getPointByIdExercises($id){
        $db= Zend_Registry::get('connectDB');
        $mysql= $db->select()
		            ->from('point')
		            ->join(array('sv' => 'students'),'sv.id = point.id_students',array('name_sv' => 'sv.name',
								 'shsv_sv' => 'sv.shsv','class_sv' => 'sv.class','course_sv' => 'sv.course'))
					->join(array('e' => 'exercises'),'e.id = point.id_exercises',array('e_name' => 'e.name'))
		            ->where('id_exercises=?',$id);
        $list= $db->fetchAll($mysql);
        return $list;
    }

    /*
     * Ham lay thong tin về điểm của sinh vien qua id mon hoc
     */  
    public function getPointByIdSubject($id){
        $db= Zend_Registry::get('connectDB');
        $mysql= $db->select()
		            ->from('point')
		            ->join(array('sv' => 'students'),'sv.id = point.id_students',array('name_sv' => 'sv.name',
								 'shsv_sv' => 'sv.shsv','class' => 'sv.class'))
		            ->where('point.id_subjects=?',$id);
        $list= $db->fetchAll($mysql);
        return $list;
    }

    

    /*
     * Ham lay thong tin về điểm  của sinh vien qua id mon hoc va loai diem (diem giua ky va cuoi ky)
     */  
    public function getPointByIdSubjectAndInfoExtend($id,$typePoint){
        $db= Zend_Registry::get('connectDB');
        $mysql= $db->select()
		            ->from('point')
		            ->join(array('sv' => 'students'),'sv.id = point.id_students',array('name_sv' => 'sv.name',
								 'shsv_sv' => 'sv.shsv','class' => 'sv.class'))
		            ->where('point.id_subjects=?',$id)
		            ->where('point.info_extend=?',$typePoint);
        $list= $db->fetchAll($mysql);
        return $list;
    }

    /*
	* Ham chinh sua diem sinh vien
 	*/

 	static function editPointOfStudent($point,$note,$id_sv,$id_ex,$id_subjects,$info_extend){
 		$db = Zend_Registry::get('connectDB');
 		$mysql = $db -> select()
			 		->from('point')
			 		->where("id_students =?",$id_sv)
			 		->where("id_exercises =?",$id_ex);
 		$list = $db->fetchAll($mysql);
 	//	echo count($list);die;
 	//	Zend_Debug::dump($list);die;
 		if(count($list)){
 			// Da co diem, chinh sua
 			$model = new PointModel();
			$data = array('point'=>$point,'note'=>$note);				
			$id = $model->update($data,"id=".$list[0]["id"]);
			
 		}
 		else{
 			// insert
 			$model = new PointModel();
			$data = array('point'=>$point,'note'=>$note,'id_students'=>$id_sv,'id_exercises'=>$id_ex,'id_subjects'=>$id_subjects,'info_extend'=>$info_extend);				
			$id = $model->insert($data);
 		}
 	}

 	static function editPointSubjectOfStudent($point,$note,$id_sv,$id_ex,$id_subjects,$info_extend){
 		$db = Zend_Registry::get('connectDB');
 		$mysql = $db -> select()
			 		->from('point')
			 		->where("id_students =?",$id_sv)
			 		->where("info_extend =?",$info_extend)
			 		->where("id_subjects =?",$id_subjects);
 		$list = $db->fetchAll($mysql);
 	//	echo count($list);die;
 	//	Zend_Debug::dump($list);die;
 		if(count($list)){
 			// Da co diem, chinh sua
 			$model = new PointModel();
			$data = array('point'=>$point,'note'=>$note,'info_extend'=>$info_extend);				
			$id = $model->update($data,"id=".$list[0]["id"]);
			
 		}
 		else{
 			// insert
 			$model = new PointModel();
			$data = array('point'=>$point,'note'=>$note,'id_students'=>$id_sv,'id_exercises'=>$id_ex,'id_subjects'=>$id_subjects,'info_extend'=>$info_extend);				
			$id = $model->insert($data);
 		}
 	}
    
      //backcx
     
     public function getDb($stu_id,$ex_id,$sub_id,$info_extend){
    //	echo $stu_id.'|'.$ex_id.'|'.$sub_id;die;
        $db = Zend_Registry::get('connectDB');
 		$mysql = $db -> select()
			 		->from('point')
			 		->where("id_students=?",$stu_id)
                    ->where("id_exercises=?",$ex_id)
			 		->where("id_subjects =?",$sub_id)
                    ->where("info_extend =?",$info_extend);
 		$list = $db->fetchAll($mysql);
        return $list;
     }
     
      public function getDbByIdSub($sub_id,$info_extend){
    //	echo $stu_id.'|'.$ex_id.'|'.$sub_id;die;
        $db = Zend_Registry::get('connectDB');
 		$mysql = $db -> select()
			 		->from('point')
                    ->joinLeft('students','point.id_students=students.id')
			 		->where("id_subjects =?",$sub_id)
                    ->where("info_extend =?",$info_extend);
                    
 		$list = $db->fetchAll($mysql);
        return $list;
     }

      public function getDbByIdSubNoJoin($sub_id,$info_extend){
    //	echo $stu_id.'|'.$ex_id.'|'.$sub_id;die;
        $db = Zend_Registry::get('connectDB');
 		$mysql = $db -> select()
			 		->from('point')
			 		->where("id_subjects =?",$sub_id)
                    ->where("info_extend =?",$info_extend);
                    
 		$list = $db->fetchAll($mysql);
        return $list;
     }
     
     public function getDbByIdStudent($id_sv){
        $db = Zend_Registry::get('connectDB');
 		$mysql = $db -> select()
			 		->from('info_class')
                    ->join('point','info_class.id_students=point.id_students',array('point','id_exercises'))
                    ->join('subjects','info_class.id_subjects=subjects.id',array('code','name'))
			 		->where("info_class.id_students =?",$id_sv)
                    ->where('point.id_subjects=?',0);
                    
                    
 		$list = $db->fetchAll($mysql);
        return $list;
     }
}